package oamMyvisit;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import javax.naming.NamingException;

import onlineAccessManagement.DBLib;

public class VisitorDao {
	
	Connection conn;
	PreparedStatement ps;
	String sql="";
	
	
	public VisitorDao() throws NamingException, SQLException {
		
		conn=DBLib.getconn();
		sql="use Visitor";
		ps=conn.prepareStatement(sql);
		ps.executeUpdate();
	}
	
	
//保存到数据库中
	
	public int saveVisitor(Visitor v) throws SQLException {
		
		sql="INSERT INTO visitors (UserID, VisitTime, LeftTime, ip,comefrom) VALUES (?, ?, ?, ?,?)";
		ps=conn.prepareStatement(sql);
		ps.setInt(1, v.getUserId());
		ps.setTimestamp(2, new Timestamp(v.getVisitTime().getTime()));
		
		if(v.getLeftTime()!=null) {
			ps.setTimestamp(3, new Timestamp(v.getLeftTime().getTime()));
		}
		else {
			ps.setTimestamp(3, null);			
		}
		
		ps.setString(4, v.getIp());
		ps.setString(5, v.getComeFrom());
		ps.executeUpdate();
		
//返回id
		
		sql="SELECT max(id) FROM visitors";	//返回当前的id
		ps=conn.prepareStatement(sql);
		ResultSet rs=ps.executeQuery();
		rs.next();
		return rs.getInt(1);
	}
	
	
//在线用户显示在页面上
	
	public ArrayList<Visitor> getVisitor() throws SQLException {
		
		ArrayList<Visitor> visitors=new ArrayList<Visitor>();
		
		sql="SELECT * FROM visitors";
		ps=conn.prepareStatement(sql);
		ResultSet rs=ps.executeQuery();
				
		while(rs.next()) {
			Visitor visitor=new Visitor();

			visitor.setId(rs.getInt(1));
			visitor.setUserId(rs.getInt(2));
			visitor.setVisitTime(rs.getTimestamp(3));					
									//用Timestamp,时间戳			
			visitor.setLeftTime(rs.getTimestamp(4));
			visitor.setIp(rs.getString(5));
			visitor.setComeFrom(rs.getString(6));
			visitors.add(visitor);
			
		}
		conn.close();
		return visitors;
	}
	
	
//记录离开的时间
	
	public void updateVisitor(Visitor v) throws SQLException, NamingException {
		if(conn.isClosed()) {
			conn=DBLib.getconn();
		}
		
		sql="UPDATE visitors SET userId=?,leftTime=? WHERE id=?";
		ps=conn.prepareStatement(sql);
		ps.setInt(1, v.getUserId());
		if(v.getLeftTime()==null) {
			ps.setTimestamp(2, null);
		}else {
			ps.setTimestamp(2, new Timestamp(v.getLeftTime().getTime()));
		}
		ps.setInt(3, v.getId());
		ps.executeUpdate();
		conn.close();
	}
	
	
	
}
